package cn.com.KnowNews.dao.daoImpl;

import cn.com.KnowNews.dao.INewsDao;
import cn.com.KnowNews.entity.News;
import cn.com.KnowNews.utils.DBConn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class NewsDaoImpl implements INewsDao {
    private Connection conn;
    private PreparedStatement ps;

    @Override
    public List<News> showAllNews() throws Exception {

        conn= DBConn.getConnection();
        String sql = "select * from t_news ";

        List<News> newslist = new ArrayList<News>();
        ps=conn.prepareStatement(sql);
        ResultSet resultSetNews = ps.executeQuery();

        while (resultSetNews.next()){

            News news = new News();

            news.setId(resultSetNews.getInt("id"));
            news.setTitle(resultSetNews.getString("title"));
            news.setAuthor(resultSetNews.getString("author"));
            news.setDatetime(resultSetNews.getString("datetime"));
            news.setAdder(resultSetNews.getString("adder"));
            news.setPicadr(resultSetNews.getString("picadr"));
            news.setMain(resultSetNews.getString("main"));
            news.setCategory(resultSetNews.getString("category"));
            newslist.add(news);
        }
        DBConn.release(resultSetNews,ps,conn);
        return newslist;
    }

    @Override
    public boolean addNew(News news) throws Exception {

        conn = DBConn.getConnection();
        String sql = "insert into t_news (title,author,datetime,adder,picadr,main,category) values (?,?,?,?,?,?,?)";

        ps=conn.prepareStatement(sql);
        ps.setString(1,news.getTitle());
        ps.setString(2,news.getAuthor());
        ps.setString(3,news.getDatetime());
        ps.setString(4,news.getAdder());
        ps.setString(5,news.getPicadr());
        ps.setString(6,news.getMain());
        ps.setString(7,news.getCategory());
        int result=ps.executeUpdate();
        DBConn.release(ps,conn);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }

    @Override
    public int delNew(int id) throws Exception {
        conn=DBConn.getConnection();
        String sql = "delete from t_news where id = ?";
        ps=conn.prepareStatement(sql);
        ps.setInt(1,id);
        int result = ps.executeUpdate();
        DBConn.release(ps,conn);
        return result;
    }

    @Override
    public int UpDateNews(News news) throws Exception {
        conn=DBConn.getConnection();
        String sql = "update t_news set title=?,author=?,datetime=?,adder=?,picadr=?,main=?,category=? where id=?";
        List<News> newslist = new ArrayList<News>();
        ps=conn.prepareStatement(sql);
        ps.setString(1,news.getTitle());
        ps.setString(2,news.getAuthor());
        ps.setString(3,news.getDatetime());
        ps.setString(4,news.getAdder());
        ps.setString(5,news.getPicadr());
        ps.setString(6,news.getMain());
        ps.setString(7,news.getCategory());
        ps.setInt(8,news.getId());
        int result = ps.executeUpdate();
        DBConn.release(ps,conn);
        return  result;
    }

    @Override
    public News showNews(int id) throws Exception {
        conn=DBConn.getConnection();
        String sql ="select * from t_news where id=?";
        ps=conn.prepareStatement(sql);
        ps.setInt(1,id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            News news = new News();
            news.setId(rs.getInt("id"));
            news.setTitle(rs.getString("title"));
            news.setAuthor(rs.getString("author"));
            news.setDatetime(rs.getString("datetime"));
            news.setAdder(rs.getString("adder"));
            news.setPicadr(rs.getString("picadr"));
            news.setMain(rs.getString("main"));
            news.setCategory(rs.getString("category"));
            DBConn.release(ps,conn);
            return news;
        }else{
            return null;
        }
    }

    @Override
    public List<News> selectByCate(String cate) throws Exception {
        conn=DBConn.getConnection();
        String sql ="select * from t_news where category=?";
        ps=conn.prepareStatement(sql);
        ps.setString(1,cate);
        List<News> newslist = new ArrayList<News>();
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            News news = new News();
            news.setId(rs.getInt("id"));
            news.setTitle(rs.getString("title"));
            news.setAuthor(rs.getString("author"));
            news.setDatetime(rs.getString("datetime"));
            news.setAdder(rs.getString("adder"));
            news.setPicadr(rs.getString("picadr"));
            news.setMain(rs.getString("main"));
            news.setCategory(rs.getString("category"));
            newslist.add(news);
        }
        DBConn.release(rs,ps,conn);
        return newslist;
    }
}
